/*
	exec sp_get_binning_info_by_process 'Sort','0','0','0','','Summary','0','0','XLReport','0'
	exec sp_get_binning_info_by_process 'Sort','0','0','0','','Detail','0','0','XLReport','0'
	exec sp_get_binning_info_by_process 'ft','0','0','0','0','0','0','0','XLReport','0'
*/
create Proc sp_get_binning_info_by_process

@ProcessType		Varchar(50),
@ProcessstepIds		Varchar(200),
@Vendor			Varchar(50),
@LotCategory		Varchar(20),
@SearchPartNo		Varchar(50),
@IsDetail		Varchar(50),
@fromDate		Varchar(50),
@toDate			Varchar(50),
@reportType		Varchar(50),
@DefaultPrompt		Varchar(50)

AS
BEGIN
	CREATE TABLE #BinningInfoTable
		( [Good Die Qty] Int, [Yield] Float, [Bin 1] Int, [Bin 2] Int, [Bin 3] Int, [Bin 4] Int, [Bin 5] Int, [Reject Qty] Int, [Wafer Id] Int,[Qty_In] int,
		  [Lot Number] Varchar(50), [Fab Lot Number] Varchar(50), [Lot Category] Varchar(50),[Date In] DateTime, [Date Out] DateTime,[Lot Status] Varchar(50),
		  [Part Number] Varchar(50),[Base Product] Varchar(50), [Ps Type] Varchar(50),[Ps Type Name] Varchar(50),[Vendor] Varchar(50),[Gross Die] Float
		)

	DECLARE @SortSqlString Nvarchar(4000)
	DECLARE @FTSqlString Nvarchar(4000)

	--For Sort Binning Information
	SET @SortSqlString = N' INSERT into #BinningInfoTable 
	SELECT  
		Good_Dice,Yield,Bin_1,Bin_2,Bin_3,Bin_4,Bin_5,Bin_Rejected,Wafer_id,( isnull(Good_Dice,0)+isnull(Bin_Rejected,0) ),
		lot_number,fab_lot_number,lot_category,date_in,date_out,lot_current_status,
		part_number,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT,
		ps_type,ps_type_name,l_vendor_name,null
	FROM 
		Informative_Binning tib,LOT_TABLE LT, product p,PROCESS_STEP PS,location l 
	WHERE 
		tib.lot_id = lt.lot_id
		AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = Ps.PROCESSSTEP_ID
		AND lt.location_id = l.location_id
		AND ( p_type is null or p_type = ''board'' or p_type = ''kit'' ) '

	IF @ProcessstepIds IS NOT NULL AND @ProcessstepIds != '0'
	BEGIN
		SET @SortSqlString = @SortSqlString + ' and LT.Processstep_id in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+@ProcessstepIds+'''))'
	END

	IF @Vendor IS NOT NULL AND @Vendor != '0'
	BEGIN
		SET @SortSqlString = @SortSqlString + ' and L.Vendor_Id = '''+ @Vendor +''''
	END

	IF @LotCategory IS NOT NULL AND @LotCategory != '0'
	BEGIN
		SET @SortSqlString = @SortSqlString + ' and Lt.Lot_Category = '''+ @LotCategory +''''
	END

	IF @SearchPartNo IS NOT NULL AND LEN(@SearchPartNo) > '0'
	BEGIN
		SET @SortSqlString = @SortSqlString + ' and P.Part_Number LIKE ''' + '%' + @SearchPartNo + '%' + ''''
	END

	IF ( @fromDate IS NOT NULL AND @fromDate <> '0' )
	BEGIN
		SET @SortSqlString = @SortSqlString +' AND date_in >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END

	IF ( @toDate IS NOT NULL AND @toDate <> '0' )
	BEGIN
		SET @SortSqlString = @SortSqlString + +' AND date_in <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END

	--PRINT '@SortSqlString :- '+@SortSqlString
	
	--For FT Binning Information
	SET @FTSqlString = N' INSERT into #BinningInfoTable 
	SELECT  
		Good_Dice,Yield,Bin_1,Bin_2,Bin_3,Bin_4,Bin_5,Bin_Rejected,null,( isnull(Good_Dice,0)+isnull(Bin_Rejected,0) ),
		lot_number,fab_lot_number,lot_category,date_in,date_out,lot_current_status,
		part_number,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT,
		ps_type,ps_type_name,l_vendor_name,null
	FROM 
		Test_Informative_Binning tib,LOT_TABLE LT, product p,PROCESS_STEP PS,location l 
	WHERE 
		tib.lot_id = lt.lot_id
		AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = Ps.PROCESSSTEP_ID
		AND lt.location_id = l.location_id
		AND ( p_type is null or p_type = ''board'' or p_type = ''kit'' ) '

	IF @ProcessstepIds IS NOT NULL AND @ProcessstepIds != '0'
	BEGIN
		SET @FTSqlString = @FTSqlString + ' and LT.Processstep_id in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+@ProcessstepIds+'''))'
	END

	IF @Vendor IS NOT NULL AND @Vendor != '0'
	BEGIN
		SET @FTSqlString = @FTSqlString + ' and L.Vendor_Id = '''+ @Vendor +''''
	END

	IF @LotCategory IS NOT NULL AND @LotCategory != '0'
	BEGIN
		SET @FTSqlString = @FTSqlString + ' and Lt.Lot_Category = '''+ @LotCategory +''''
	END

	/*IF @Status IS NOT NULL AND @Status != '0'
	BEGIN
		SET @SqlString = @SqlString + ' LT.Processstep_id = '
	END*/

	IF @SearchPartNo IS NOT NULL AND LEN(@SearchPartNo) > '0'
	BEGIN
		SET @FTSqlString = @FTSqlString + ' and P.Part_Number LIKE ''' + '%' + @SearchPartNo + '%' + ''''
	END
	
	IF ( @fromDate IS NOT NULL AND @fromDate <> '0' )
	BEGIN
		SET @FTSqlString = @FTSqlString +' AND date_in >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END

	IF ( @toDate IS NOT NULL AND @toDate <> '0' )
	BEGIN
		SET @FTSqlString = @FTSqlString + +' AND date_in <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	--PRINT '@FTSqlString :- '+@FTSqlString

	IF @ProcessType = 'Sort'
	BEGIN
		EXEC (@SortSqlString)
	END
	ELSE IF @ProcessType = 'FT'
	BEGIN
		EXEC (@FTSqlString)
	END
	
	IF @reportType = 'XLReport'
	BEGIN
		IF ( @ProcessType = 'Sort' AND @IsDetail = 'Summary' )
		BEGIN
			Select 
				[Base Product],[Part Number],[Fab Lot Number],[Lot Number],Min([Date In]) As [Date In], Min([Date Out]) as [Date Out],[Vendor],/*count([Wafer Id]) as [Wafer Id],*/Sum([Qty_In]) as [Qty_In],
				Sum([Good Die Qty]) as [Good Die Qty],Avg([Yield]) as [Yield],Sum([Bin 1]) as [Bin 1],Sum([Bin 2]) as [Bin 2],Sum([Bin 3]) as [Bin 3],Sum([Bin 4]) as [Bin 4],Sum([Bin 5]) as [Bin 5],Sum([Reject Qty]) as [Reject Qty],
				[Lot Category]--,[Lot Status],[Ps Type],[Ps Type Name],[Gross Die]
			from 
				#BinningInfoTable
			Group By
				[Base Product],[Part Number],[Fab Lot Number],[Lot Number],[Vendor],[Lot Category]--,[Lot Status],[Ps Type],[Ps Type Name],[Gross Die]
			Order By
				[Base Product],[Part Number],[Fab Lot Number],[Lot Number]
		END
		ELSE IF ( @ProcessType = 'Sort' AND @IsDetail = 'Detail' )
		BEGIN
			Select 
				[Base Product],[Part Number],[Fab Lot Number],[Lot Number],[Date In],[Date Out],[Vendor],[Wafer Id],[Qty_In],
				[Good Die Qty],[Yield],[Bin 1],[Bin 2],[Bin 3],[Bin 4],[Bin 5],[Reject Qty],[Lot Category]
			from 
				#BinningInfoTable
			Order By
				[Base Product],[Part Number],[Lot Number],[Wafer Id]
		END
		ELSE 
		BEGIN
			Select 
				[Base Product],[Part Number],[Fab Lot Number],[Lot Number],[Date In],[Date Out],[Vendor]/*,[Wafer Id],*/[Qty_In],
				[Good Die Qty],[Yield],[Bin 1],[Bin 2],[Bin 3],[Bin 4],[Bin 5],[Reject Qty],[Lot Category]
			from 
				#BinningInfoTable
			Order By
				[Base Product],[Part Number],[Lot Number],[Wafer Id]
		END
	END
	ELSE
	BEGIN
		IF @IsDetail = 'Summary'
		BEGIN
			Select 
				[Base Product],[Part Number],[Lot Number],[Fab Lot Number],[Vendor],Min([Date In]) As [Date In], Min([Date Out]) as [Date Out],count([Wafer Id]) as [Wafer Id],Sum([Qty_In]) as [Qty_In],
				Sum([Good Die Qty]) as [Good Die Qty],Sum([Bin 1]) as [Bin 1],Sum([Bin 2]) as [Bin 2],Sum([Bin 3]) as [Bin 3],Sum([Bin 4]) as [Bin 4],Sum([Bin 5]) as [Bin 5],Sum([Reject Qty]) as [Reject Qty],
				[Lot Category],[Lot Status],[Ps Type],[Ps Type Name],[Gross Die],Avg([Yield]) as [Yield]
			from 
				#BinningInfoTable
			Group By
				[Base Product],[Part Number],[Lot Number],[Fab Lot Number],[Vendor],[Lot Category],[Lot Status],[Ps Type],[Ps Type Name],[Gross Die]
			Order By
				[Base Product],[Part Number],[Lot Number]
		END
		ELSE
		BEGIN
			Select 
				[Base Product],[Part Number],[Lot Number],[Fab Lot Number],[Vendor],[Date In],[Date Out],[Wafer Id],[Qty_In],
				[Good Die Qty],[Bin 1],[Bin 2],[Bin 3],[Bin 4],[Bin 5],[Reject Qty],[Lot Category],[Lot Status],[Ps Type],[Ps Type Name],[Gross Die],[Yield]
			from 
				#BinningInfoTable
			Order By
				[Base Product],[Part Number],[Lot Number],[Wafer Id]
		END
	END
END



